package com.ph.irp.base.verifyproject.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.ph.irp.base.verifyproject.dao.PBaseVerifyprojectCandiateDao;
import com.ph.irp.model.PBaseVerifyprojectCandiate;
import com.ph.util.DBUtil;
import com.ph.util.DateUtil;

public class PBaseVerifyprojectCandiateDaoImpl implements
		PBaseVerifyprojectCandiateDao {
	private ComboPooledDataSource dataSource;
	
	public void setDataSource(ComboPooledDataSource dataSource) {
		this.dataSource = dataSource;
	}

	@Override
	public void addCandiate(PBaseVerifyprojectCandiate candiate) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		
		StringBuilder sb = new StringBuilder();
		sb.append("insert into p_base_verifyproject_candiate(CandiateCode,CandiateName");
		if(candiate.getPBaseAssistantmaterialCandiatecategoryId() != 0){
			sb.append(",CandiateCategoryID");
		}
		if(candiate.getPBaseVerifyprojectDepartmentId() != 0){
			sb.append(",DepartmentID");
		}
		sb.append(",Gender");
		if(candiate.getBithDate() != null && !candiate.getBithDate().equals("")){
			sb.append(",BithDate");
		}
		if(candiate.getPBaseAssistantmaterialCultureId() != 0){
			sb.append(",CultureID");
		}
		if(candiate.getMobile() != null && !candiate.getMobile().equals("")){
			sb.append(",Mobile");
		}
		if(candiate.getIdcardNo() != null && !candiate.getIdcardNo().equals("")){
			sb.append(",IDCardNo");
		}
		if(candiate.getPBaseAssistantmaterialDutyId() != 0){
			sb.append(",DutyID");
		}
		if(candiate.getEnterDate() != null && !candiate.getEnterDate().equals("")){
			sb.append(",enterDate");
		}
		if(candiate.getLeaveDate() != null && !candiate.getLeaveDate().equals("")){
			sb.append(",leaveDate");
		}
		if(candiate.getOpenAccountBank() != null && !candiate.getOpenAccountBank().equals("")){
			sb.append(",OpenAccountBank");
		}
		if(candiate.getAddress() != null && !candiate.getAddress().equals("")){
			sb.append(",Address");
		}
		if(candiate.getEmail() != null && !candiate.getEmail().equals("")){
			sb.append(",Email");
		}
		if(candiate.getMemo() != null && !candiate.getMemo().equals("")){
			sb.append(",Memo");
		}
		if(candiate.getAllowance() != 0){
			sb.append(",Allowance");
		}
		sb.append(") values(");
		sb.append("'");
		sb.append(candiate.getCandiateCode().trim());
		sb.append("'");
		sb.append(",'");
		sb.append(candiate.getCandiateName().trim());
		sb.append("'");
		if(candiate.getPBaseAssistantmaterialCandiatecategoryId() != 0){
			sb.append(",");
			sb.append(candiate.getPBaseAssistantmaterialCandiatecategoryId());
		}
		if(candiate.getPBaseVerifyprojectDepartmentId() != 0){
			sb.append(",");
			sb.append(candiate.getPBaseVerifyprojectDepartmentId());
		}
		sb.append(",");
		sb.append(candiate.getGender());
		if(candiate.getBithDate() != null && !candiate.getBithDate().equals("")){
			sb.append(",'");
			sb.append(candiate.getBithDate());
			sb.append("'");
		}
		if(candiate.getPBaseAssistantmaterialCultureId() != 0){
			sb.append(",");
			sb.append(candiate.getPBaseAssistantmaterialCultureId());
		}
		if(candiate.getMobile() != null && !candiate.getMobile().equals("")){
			sb.append(",'");
			sb.append(candiate.getMobile().trim());
			sb.append("'");
		}
		if(candiate.getIdcardNo() != null && !candiate.getIdcardNo().equals("")){
			sb.append(",'");
			sb.append(candiate.getIdcardNo().trim());
			sb.append("'");
		}
		if(candiate.getPBaseAssistantmaterialDutyId() != 0){
			sb.append(",");
			sb.append(candiate.getPBaseAssistantmaterialDutyId());
		}
		if(candiate.getEnterDate() != null && !candiate.getEnterDate().equals("")){
			sb.append(",'");
			sb.append(candiate.getEnterDate());
			sb.append("'");
		}
		if(candiate.getLeaveDate() != null && !candiate.getLeaveDate().equals("")){
			sb.append(",'");
			sb.append(candiate.getLeaveDate());
			sb.append("'");
		}
		if(candiate.getOpenAccountBank() != null && !candiate.getOpenAccountBank().equals("")){
			sb.append(",'");
			sb.append(candiate.getOpenAccountBank().trim());
			sb.append("'");
		}
		if(candiate.getAddress() != null && !candiate.getAddress().equals("")){
			sb.append(",'");
			sb.append(candiate.getAddress().trim());
			sb.append("'");
		}
		if(candiate.getEmail() != null && !candiate.getEmail().equals("")){
			sb.append(",'");
			sb.append(candiate.getEmail().trim());
			sb.append("'");
		}
		if(candiate.getMemo() != null && !candiate.getMemo().equals("")){
			sb.append(",'");
			sb.append(candiate.getMemo().trim());
			sb.append("'");
		}
		if(candiate.getAllowance() != 0){
			sb.append(",");
			sb.append(candiate.getAllowance());
		}
		sb.append(")");

		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

	@Override
	public void deleteCandiate(int candiateId) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		
		String sql = "delete from p_base_verifyproject_candiate where CandiateID=?";
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sql);
			pStmt.setInt(1, candiateId);
			pStmt.execute();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

	@Override
	public PBaseVerifyprojectCandiate getCandiateById(int candiateId) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		PBaseVerifyprojectCandiate candiate = null;
		String sql = "select * from p_base_verifyproject_candiate where CandiateID=?";
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sql);
			pStmt.setInt(1, candiateId);
			rs = pStmt.executeQuery();
			if(rs.next()){
				candiate = new PBaseVerifyprojectCandiate();
				candiate.setCandiateId(rs.getInt("candiateId"));
				candiate.setCandiateCode(rs.getString("CandiateCode"));
				candiate.setCandiateName(rs.getString("CandiateName"));
				candiate.setPBaseAssistantmaterialCandiatecategoryId(rs.getInt("CandiateCategoryID"));
				candiate.setPBaseVerifyprojectDepartmentId(rs.getInt("DepartmentID"));
				candiate.setGender(rs.getInt("Gender"));
				candiate.setBithDate(DateUtil.date2String(rs.getDate("BithDate"), "yyyy-MM-dd"));
				candiate.setPBaseAssistantmaterialCultureId(rs.getInt("CultureID"));
				candiate.setMobile(rs.getString("Mobile"));
				candiate.setIdcardNo(rs.getString("IDCardNo"));
				candiate.setPBaseAssistantmaterialDutyId(rs.getInt("DutyID"));
				candiate.setEnterDate(DateUtil.date2String(rs.getDate("enterDate"), "yyyy-MM-dd"));
				candiate.setLeaveDate(DateUtil.date2String(rs.getDate("leaveDate"), "yyyy-MM-dd"));
				candiate.setOpenAccountBank(rs.getString("OpenAccountBank"));
				candiate.setAddress(rs.getString("Address"));
				candiate.setEmail(rs.getString("Email"));
				candiate.setMemo(rs.getString("Memo"));
				candiate.setAllowance(rs.getInt("Allowance"));
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
		return candiate;
	}

	@Override
	public List<PBaseVerifyprojectCandiate> getCandiateList(PBaseVerifyprojectCandiate p) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		List<PBaseVerifyprojectCandiate> list = new ArrayList<PBaseVerifyprojectCandiate>();
		
		StringBuilder sb = new StringBuilder();
		sb.append("select * from p_base_verifyproject_candiate where 1=1");	
		if(p!=null){
			if(p.getCandiateId() != 0){
				sb.append(" and candiateId!=");
				sb.append(p.getCandiateId());
			}
			if(p.getCandiateCode() != null && !p.getCandiateCode().equals("")){
				sb.append(" and CandiateCode like '%");
				sb.append(p.getCandiateCode());
				sb.append("%'");
			}
			if(p.getCandiateName() != null && !p.getCandiateName().equals("")){
				sb.append(" and CandiateName like '%");
				sb.append(p.getCandiateName());
				sb.append("%'");
			}
			if(p.getPBaseVerifyprojectDepartmentId() != 0){
				sb.append(" and DepartmentID=");
				sb.append(p.getPBaseVerifyprojectDepartmentId());
			}
			if(p.getPBaseAssistantmaterialDutyId() != 0){
				sb.append(" and DutyID=");
				sb.append(p.getPBaseAssistantmaterialDutyId());
			}
		}
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();
			while(rs.next()){
				PBaseVerifyprojectCandiate pbvc = new PBaseVerifyprojectCandiate();
				pbvc.setCandiateId(rs.getInt("candiateId"));
				pbvc.setCandiateCode(rs.getString("CandiateCode"));
				pbvc.setCandiateName(rs.getString("CandiateName"));
				pbvc.setPBaseAssistantmaterialCandiatecategoryId(rs.getInt("CandiateCategoryID"));
				pbvc.setPBaseVerifyprojectDepartmentId(rs.getInt("DepartmentID"));
				pbvc.setGender(rs.getInt("Gender"));
				pbvc.setBithDate(DateUtil.date2String(rs.getDate("BithDate"), "yyyy-MM-dd"));
				pbvc.setPBaseAssistantmaterialCultureId(rs.getInt("CultureID"));
				pbvc.setMobile(rs.getString("Mobile"));
				pbvc.setIdcardNo(rs.getString("IDCardNo"));
				pbvc.setPBaseAssistantmaterialDutyId(rs.getInt("DutyID"));
				pbvc.setEnterDate(DateUtil.date2String(rs.getDate("enterDate"), "yyyy-MM-dd"));
				pbvc.setLeaveDate(DateUtil.date2String(rs.getDate("leaveDate"), "yyyy-MM-dd"));
				pbvc.setOpenAccountBank(rs.getString("OpenAccountBank"));
				pbvc.setAddress(rs.getString("Address"));
				pbvc.setEmail(rs.getString("Email"));
				pbvc.setMemo(rs.getString("Memo"));
				pbvc.setAllowance(rs.getInt("Allowance"));
				list.add(pbvc);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
		return list;
	}

	@Override
	public boolean isSame(String candiateCode) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		
		String sql = "select * from p_base_verifyproject_candiate where CandiateCode='"+candiateCode.trim()+"'";
		
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sql);
			rs = pStmt.executeQuery();
			if(rs.next()){
				return true;
			}else{
				return false;
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

	@Override
	public void updateCandiate(PBaseVerifyprojectCandiate candiate) throws Throwable{
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;	
		StringBuilder sb = new StringBuilder();
		sb.append("update p_base_verifyproject_candiate set CandiateCode='");
		sb.append(candiate.getCandiateCode().trim()).append("',CandiateName='");
		sb.append(candiate.getCandiateName().trim()).append("'");
		if(candiate.getPBaseAssistantmaterialCandiatecategoryId() != 0){
			sb.append(",CandiateCategoryID=").append(candiate.getPBaseAssistantmaterialCandiatecategoryId());
		}
		if(candiate.getPBaseVerifyprojectDepartmentId() != 0){
			sb.append(",DepartmentID=").append(candiate.getPBaseVerifyprojectDepartmentId());
		}
		sb.append(",Gender=").append(candiate.getGender());
		if(candiate.getBithDate()!= null && !candiate.getBithDate().equals(""))
		{
	    sb.append(",BithDate='").append(candiate.getBithDate()).append("'");
	    }
		else {
			sb.append(",BithDate=null");
			}
		if(candiate.getPBaseAssistantmaterialCultureId() != 0){
			sb.append(",CultureID=").append(candiate.getPBaseAssistantmaterialCultureId());
		}
			sb.append(",Mobile='").append(candiate.getMobile().trim()).append("'");
			sb.append(",IDCardNo='").append(candiate.getIdcardNo().trim()).append("'");
		if(candiate.getPBaseAssistantmaterialDutyId() != 0){
			sb.append(",DutyID=").append(candiate.getPBaseAssistantmaterialDutyId());
		}
		if(candiate.getEnterDate()!= null && !candiate.getEnterDate().equals(""))
		{
		    sb.append(",enterDate='").append(candiate.getEnterDate()).append("'");
		}
		else{
			sb.append(",enterDate=null");
		}
		if(candiate.getLeaveDate()!= null && !candiate.getLeaveDate().equals(""))
		{
        	sb.append(",leaveDate='").append(candiate.getLeaveDate()).append("'");
		}
		else{
			sb.append(",leaveDate=null");
		}
		    sb.append(",OpenAccountBank='").append(candiate.getOpenAccountBank().trim()).append("'");
		    sb.append(",Address ='").append(candiate.getAddress().trim()).append("'");
			sb.append(",Email='").append(candiate.getEmail().trim()).append("'");
			sb.append(",Memo='").append(candiate.getMemo().trim()).append("'");
			sb.append(",Allowance= ").append(candiate.getAllowance());
			sb.append(" where CandiateID=").append(candiate.getCandiateId());
		try {
			con = dataSource.getConnection();
			//System.out.println(sb.toString());
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
		
	}

	@Override
	public List<PBaseVerifyprojectCandiate> getCandiateByCode(String code)
			throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		List<PBaseVerifyprojectCandiate> list = new ArrayList<PBaseVerifyprojectCandiate>();
		
		StringBuilder sb = new StringBuilder();
		sb.append("select * from p_base_verifyproject_candiate where CandiateCode like '%"+code+"%'");
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();
			while(rs.next()){
				PBaseVerifyprojectCandiate pbvc = new PBaseVerifyprojectCandiate();
				pbvc.setCandiateId(rs.getInt("candiateId"));
				pbvc.setCandiateCode(rs.getString("CandiateCode"));
				pbvc.setCandiateName(rs.getString("CandiateName"));
				pbvc.setPBaseAssistantmaterialCandiatecategoryId(rs.getInt("CandiateCategoryID"));
				pbvc.setPBaseVerifyprojectDepartmentId(rs.getInt("DepartmentID"));
				pbvc.setGender(rs.getInt("Gender"));
				pbvc.setBithDate(DateUtil.date2String(rs.getDate("BithDate"), "yyyy-MM-dd"));
				pbvc.setPBaseAssistantmaterialCultureId(rs.getInt("CultureID"));
				pbvc.setMobile(rs.getString("Mobile"));
				pbvc.setIdcardNo(rs.getString("IDCardNo"));
				pbvc.setPBaseAssistantmaterialDutyId(rs.getInt("DutyID"));
				pbvc.setEnterDate(DateUtil.date2String(rs.getDate("enterDate"), "yyyy-MM-dd"));
				pbvc.setLeaveDate(DateUtil.date2String(rs.getDate("leaveDate"), "yyyy-MM-dd"));
				pbvc.setOpenAccountBank(rs.getString("OpenAccountBank"));
				pbvc.setAddress(rs.getString("Address"));
				pbvc.setEmail(rs.getString("Email"));
				pbvc.setMemo(rs.getString("Memo"));
				pbvc.setAllowance(rs.getInt("Allowance"));
				list.add(pbvc);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
		return list;
	}

	@Override
	public List<PBaseVerifyprojectCandiate> getCandiateByCodeAndName(
			String code, String name) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		List<PBaseVerifyprojectCandiate> list = new ArrayList<PBaseVerifyprojectCandiate>();
		
		StringBuilder sb = new StringBuilder();
		sb.append("select * from p_base_verifyproject_candiate where CandiateCode like '%"+code+"%' and CandiateName like '%"+name+"%'");
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();
			while(rs.next()){
				PBaseVerifyprojectCandiate pbvc = new PBaseVerifyprojectCandiate();
				pbvc.setCandiateId(rs.getInt("candiateId"));
				pbvc.setCandiateCode(rs.getString("CandiateCode"));
				pbvc.setCandiateName(rs.getString("CandiateName"));
				pbvc.setPBaseAssistantmaterialCandiatecategoryId(rs.getInt("CandiateCategoryID"));
				pbvc.setPBaseVerifyprojectDepartmentId(rs.getInt("DepartmentID"));
				pbvc.setGender(rs.getInt("Gender"));
				pbvc.setBithDate(DateUtil.date2String(rs.getDate("BithDate"), "yyyy-MM-dd"));
				pbvc.setPBaseAssistantmaterialCultureId(rs.getInt("CultureID"));
				pbvc.setMobile(rs.getString("Mobile"));
				pbvc.setIdcardNo(rs.getString("IDCardNo"));
				pbvc.setPBaseAssistantmaterialDutyId(rs.getInt("DutyID"));
				pbvc.setEnterDate(DateUtil.date2String(rs.getDate("enterDate"), "yyyy-MM-dd"));
				pbvc.setLeaveDate(DateUtil.date2String(rs.getDate("leaveDate"), "yyyy-MM-dd"));
				pbvc.setOpenAccountBank(rs.getString("OpenAccountBank"));
				pbvc.setAddress(rs.getString("Address"));
				pbvc.setEmail(rs.getString("Email"));
				pbvc.setMemo(rs.getString("Memo"));
				pbvc.setAllowance(rs.getInt("Allowance"));
				list.add(pbvc);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
		return list;
	}

	@Override
	public List<PBaseVerifyprojectCandiate> getCandiateByName(String name)
			throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		List<PBaseVerifyprojectCandiate> list = new ArrayList<PBaseVerifyprojectCandiate>();
		
		StringBuilder sb = new StringBuilder();
		sb.append("select * from p_base_verifyproject_candiate where  CandiateName like '%"+name+"%'");
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();
			while(rs.next()){
				PBaseVerifyprojectCandiate pbvc = new PBaseVerifyprojectCandiate();
				pbvc.setCandiateId(rs.getInt("candiateId"));
				pbvc.setCandiateCode(rs.getString("CandiateCode"));
				pbvc.setCandiateName(rs.getString("CandiateName"));
				pbvc.setPBaseAssistantmaterialCandiatecategoryId(rs.getInt("CandiateCategoryID"));
				pbvc.setPBaseVerifyprojectDepartmentId(rs.getInt("DepartmentID"));
				pbvc.setGender(rs.getInt("Gender"));
				pbvc.setBithDate(DateUtil.date2String(rs.getDate("BithDate"), "yyyy-MM-dd"));
				pbvc.setPBaseAssistantmaterialCultureId(rs.getInt("CultureID"));
				pbvc.setMobile(rs.getString("Mobile"));
				pbvc.setIdcardNo(rs.getString("IDCardNo"));
				pbvc.setPBaseAssistantmaterialDutyId(rs.getInt("DutyID"));
				pbvc.setEnterDate(DateUtil.date2String(rs.getDate("enterDate"), "yyyy-MM-dd"));
				pbvc.setLeaveDate(DateUtil.date2String(rs.getDate("leaveDate"), "yyyy-MM-dd"));
				pbvc.setOpenAccountBank(rs.getString("OpenAccountBank"));
				pbvc.setAddress(rs.getString("Address"));
				pbvc.setEmail(rs.getString("Email"));
				pbvc.setMemo(rs.getString("Memo"));
				pbvc.setAllowance(rs.getInt("Allowance"));
				list.add(pbvc);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
		return list;
	}

}
